package com.xuxueli.commontdemo.util;
import com.xuxueli.commontdemo.entity.ServiceColumnMappingDTO;
import com.xuxueli.commontdemo.entity.ServiceTableMappingDTO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class TestParaToSql {
    public static void main(String[] args) {
        createSQLData();
    }

    private static void createSQLData(){
        Map<Long, String> historyTables = new HashMap<>(16);
        Map<Long, Map<String, ServiceColumnMappingDTO>> historyParamColumns = new HashMap<>(16);
        Map<String, List<String>> existTableColumns = new HashMap<>(16);
        Map<String, ServiceColumnMappingDTO> columnMap;
        List<String> existColumns;
        //表里面数据
        List<ServiceTableMappingDTO> historyTableMappings = new ArrayList<>();
        if (historyTableMappings != null) {
            for (ServiceTableMappingDTO mapping : historyTableMappings) {
                columnMap = new HashMap<>(16);
                existColumns = new ArrayList<>();
                historyTables.put(mapping.getResponseParamId(), mapping.getTableName());
                for (ServiceColumnMappingDTO column : mapping.getColumnMappings()) {
                    if (verifyColumn(column)) {
                        columnMap.put(column.getColumnName(), column);
                    }
                }
                historyParamColumns.put(mapping.getResponseParamId(), columnMap);
                //根据表获取表字段
                List<Map<String, Object>> columns = new ArrayList<>();
                if (columns != null && !columns.isEmpty()) {
                    for (Map<String, Object> column : columns) {
                        existColumns.add(column.get("columnName").toString());
                    }
                    existTableColumns.put(mapping.getTableName(), existColumns);
                }
            }
        }
        //最终表里面数据
        List<ServiceTableMappingDTO> currentTableMappings = new ArrayList<>();
        String historyTableName;
        StringBuilder sqlBuilder = new StringBuilder();
        Map<String, ServiceColumnMappingDTO> historyColumns;
        ServiceColumnMappingDTO historyColumn;
        for (ServiceTableMappingDTO currentTableMapping : currentTableMappings) {
            if (currentTableMapping.getTableType() == 2) {
                continue;
            }
            historyTableName = historyTables.get(currentTableMapping.getResponseParamId());
            if (historyTableName == null) {
                sqlBuilder.append(createTableSql(currentTableMapping));
            } else {
                if (!existTableColumns.containsKey(historyTableName)) {
                    sqlBuilder.append(createTableSql(currentTableMapping));
                    continue;
                }
                existColumns = existTableColumns.get(historyTableName);
                if (!historyTableName.equals(currentTableMapping.getTableName())) {
                    sqlBuilder.append(alterTableSql(historyTableName, currentTableMapping.getTableName()));
                }
                if (currentTableMapping.getColumnMappings() == null) {
                    continue;
                }
                historyColumns = historyParamColumns.get(currentTableMapping.getResponseParamId());
                for (ServiceColumnMappingDTO columnMapping : currentTableMapping.getColumnMappings()) {
                    if (!verifyColumn(columnMapping)) {
                        continue;
                    }
                    historyColumn = historyColumns.get(columnMapping.getColumnName());
                    if (historyColumn == null) {
                        if (existColumns.contains(columnMapping.getColumnName())) {
                            sqlBuilder.append(alterColumnSql(currentTableMapping.getTableName(), columnMapping));
                        } else {
                            sqlBuilder.append(addColumnSql(currentTableMapping.getTableName(), columnMapping));
                        }
                        continue;
                    }
                    if (!existColumns.contains(columnMapping.getColumnName())) {
                        sqlBuilder.append(addColumnSql(currentTableMapping.getTableName(), columnMapping));
                        continue;
                    }
                    if (columnMapping.getColumnType().equals(historyColumn.getColumnType())) {
                        if (!columnMapping.getColumnType().contains("CLOB") && !columnMapping.getColumnType()
                                .contains("TEXT") && !columnMapping.getColumnType().contains("DATE") && !columnMapping
                                .getColumnType().contains("TIMESTAMP") && columnMapping.getColumnLength() != null
                                && !columnMapping.getColumnLength().equals(historyColumn.getColumnLength())) {
                            sqlBuilder.append(alterColumnSql(currentTableMapping.getTableName(), columnMapping));
                        }
                    } else {
                        sqlBuilder.append(alterColumnSql(currentTableMapping.getTableName(), columnMapping));
                    }
                }
            }
        }
        if (StringUtils.isNotBlank(sqlBuilder.toString())) {
            log.info("SQL执行失败,{}", sqlBuilder.toString());
        }
    }

    private static String createTableSql(ServiceTableMappingDTO tableMapping) {
        StringBuilder createBuilder;
        boolean verifyResult;
        createBuilder = new StringBuilder("drop table if exists ").append(tableMapping.getTableName())
                .append(";\ncreate table ").append(tableMapping.getTableName()).append("(")
                .append("\n\t").append("id varchar(32),");
        if (tableMapping.getColumnMappings() != null) {
            for (ServiceColumnMappingDTO columnMapping : tableMapping.getColumnMappings()) {
//                if (columnMapping.getStorage().equals(BooleanEnum.NO.getCode()) || StringUtils
//                    .isAnyBlank(columnMapping.getColumnName(), columnMapping.getColumnType())) {
//                    continue;
//                }
                verifyResult = verifyColumn(columnMapping);
                if (!verifyResult) {
                    continue;
                }
                createBuilder.append("\n\t").append("`" + columnMapping.getColumnName() + "`").append(" ")
                        .append(columnMapping.getColumnType());
                if (columnMapping.getColumnLength() != null && columnMapping.getColumnLength() > 0) {
                    createBuilder.append("(").append(columnMapping.getColumnLength()).append(")");
                }
                createBuilder.append(",");
            }
        }
        if (createBuilder.toString().lastIndexOf(",") == createBuilder.toString().length() - 1) {
            createBuilder = new StringBuilder(
                    createBuilder.toString().substring(0, createBuilder.toString().length() - 1));
        }
        createBuilder.append("\n").append(");").append("\n");
        return createBuilder.toString();
    }


    private static String alterTableSql(String before, String after) {
        StringBuilder sql = new StringBuilder("alter table ");
        sql.append(before).append(" rename ").append(after);
        return sql.append(";\n").toString();
    }


    private static String addColumnSql(String tableName, ServiceColumnMappingDTO columnMapping) {
        StringBuilder sql = new StringBuilder("alter table ");
        sql.append(tableName).append(" add ").append("`" + columnMapping.getColumnName() + "`").append(" ")
                .append(columnMapping.getColumnType());
        if (!columnMapping.getColumnType().contains("CLOB") && !columnMapping.getColumnType().contains("TEXT")
                && !columnMapping.getColumnType().contains("DATE") && !columnMapping.getColumnType()
                .contains("TIMESTAMP") && columnMapping.getColumnLength() != null) {
            sql.append("(").append(columnMapping.getColumnLength()).append(")");
        }
        return sql.append(";\n").toString();
    }

    private static String alterColumnSql(String tableName, ServiceColumnMappingDTO columnMapping) {
        StringBuilder sql = new StringBuilder("alter table ");
        sql.append(tableName).append(" modify ").append("`" + columnMapping.getColumnName() + "`").append(" ")
                .append(columnMapping.getColumnType());
        if (!columnMapping.getColumnType().contains("CLOB") && !columnMapping.getColumnType().contains("TEXT")
                && !columnMapping.getColumnType().contains("DATE") && !columnMapping.getColumnType()
                .contains("TIMESTAMP") && columnMapping.getColumnLength() != null) {
            sql.append("(").append(columnMapping.getColumnLength()).append(")");
        }
        return sql.append(";\n").toString();
    }


    private static boolean verifyColumn(ServiceColumnMappingDTO columnMapping) {
        if (columnMapping == null) {
            return false;
        }
        if (columnMapping.getStorage().equals(0) || StringUtils
                .isAnyBlank(columnMapping.getColumnName(), columnMapping.getColumnType())) {
            return false;
        }
        if (!columnMapping.getColumnType().contains("CLOB") && !columnMapping.getColumnType().contains("TEXT")
                && !columnMapping.getColumnType().contains("DATE") && !columnMapping.getColumnType()
                .contains("TIMESTAMP") && columnMapping.getColumnLength() != null) {
            return columnMapping.getColumnLength() != null && columnMapping.getColumnLength() != 0;
        }
        return true;
    }


}
